/**
 * WorxConnector.java
 *
 * Create: Mar 20, 2006
 * David Connell
 * Copyright (c) 2005 Synergy Systems, Inc.
 */
package org.puresilk.afx.data;

// java sql support 
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;

// java jndi support
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.sql.DataSource;

// third party support
import org.apache.log4j.Logger;

import org.puresilk.afx.base.SilkAfxException;

/**
 * Worx Utility JNDI Database Connector.
 *
 */
public class SilkAfxConnector {
	private Logger logger = Logger.getLogger(SilkAfxConnector.class);
	private String jndiName = null;

	/**
	 * Default Constructor 
	 */
	public SilkAfxConnector() {
		super();
	}
	
	public SilkAfxConnector(String name) {
		jndiName = name;
	}
	
	/**
	 * Get a database connection from the 
	 * control servlet....
	 * 
	 * @return
	 */
	public Connection getConnection(){
		Connection dbConn = null;
		
		try {
			// get initial context
			Context context = new InitialContext();
			// lookup the app datasource
			DataSource  ds = (DataSource)context.lookup(jndiName);
			
			// get a connection
			dbConn = ds.getConnection();
			
		}
		catch (Exception e){
			logger.error("JNDI Lookup error on " + jndiName, e);
		}
		
		return dbConn;
	}
	
	/**
	 * Return a database connection to the 
	 * control servlet....
	 */
	public synchronized void freeConnection(Connection conn){
		try { 
			// return to the pool.
			conn.close(); 
		}
		catch(Exception e){
			logger.error("Error closing JNDI/JDBC Connection", e);
		}
	}
	
	/**
	 * Get the next table id from a db sequence.
	 *  
	 * Warning: PostgreSQL specific functionality!!!
	 * 
	 * @param seqName
	 * @return next key
	 */
	public int getNextId(String seqName) {
		Connection conn = getConnection();
		Statement st = null;
		ResultSet rs = null;
		int rv = 0;
		
		try {
			st = conn.createStatement();
			rs = st.executeQuery("Select nextval('" + seqName + "') as rkey");
			rs.next();
			
			rv = rs.getInt("rkey");
			
		}
		catch (Exception sex) {
			logger.error("SQL Error getting next Emp Id",sex);
			rv = 0;
		}
		if (rs != null) try { rs.close(); } catch (Exception ex) {}
		if (st != null) try { st.close(); } catch (Exception ex) {}
		freeConnection(conn);
				
		// return new emp id
		return rv;
	}

	/**
	 * Process stored procedure that returns 
	 * an integer value.  Used to call insert 
	 * procs that return the resulting rkey.
	 * 
	 * @param sql
	 * @throws DWMSException
	 * @return The row key of the inserted record
	 */
	public int execInsertProc(String sql) throws SilkAfxException {
		int rv = 0;
		
		// sql objects
		Connection conn = getConnection();
		Statement st = null;
		ResultSet rs = null;
		try {
			st = conn.createStatement();
			rs = st.executeQuery(prepProc(sql));
			rs.next();
			rv = rs.getInt(1);
		}
		catch (Exception sex) {
			logger.error("Error Executing SQL :" + sql ,sex);
			throw new SilkAfxException("SQL Failure: " + sql); 
		}
		finally {
			if (rs != null) try { rs.close(); } catch (Exception ex) {}
			if (st != null) try { st.close(); } catch (Exception ex) {}
			freeConnection(conn);
		}

		return rv;
	}
	
	/**
	 * Process no results SQL statements
	 * (straight pass thru)
	 * 
	 * @param sql
	 * @throws SilkAfxException
	 */
	public void execSql(String sql) throws SilkAfxException {
		Connection conn = getConnection();
		Statement st = null;
				
		try {
			st = conn.createStatement();
			st.execute(sql);
		}
		catch (Exception sex) {
			logger.error("Error Executing SQL: " + sql,sex);
			throw new SilkAfxException("SQL Failure: " + sql); 
		}
		finally{
			if (st != null) try { st.close(); } catch (Exception ex) {}
			freeConnection(conn);
		}
	}

	/**
	 * Process no results SQL Procedure and will
	 * check to be sure the 'select' is in place
	 * in case a pure procedure call is sent in.
	 * 
	 * @param sql
	 * @throws DWMSException
	 */
	public void execSqlProc(String sql) throws SilkAfxException {
		Connection conn = getConnection();
		Statement st = null;
				
		try {
			st = conn.createStatement();
			st.execute(prepProc(sql));
		}
		catch (Exception sex) {
			logger.error("Error Executing SQL: " + sql,sex);
			throw new SilkAfxException("SQL Failure: " + sql); 
		}
		finally{
			if (st != null) try { st.close(); } catch (Exception ex) {}
			freeConnection(conn);
		}
	}
	
	/** 
	 * Utility function to format the call.
	 * 
	 * @param inSql
	 * @return
	 */
	private String prepProc(String inSql){
		StringBuffer outSql = new StringBuffer();
		
		// normalize input SQL
		String test = inSql.trim().toLowerCase();
		logger.debug("pp>sql: " + test);
		
		// 'Select' required (POSTGRESQL)
		if (!test.startsWith("select"))
			outSql.append("select ");
		
		// add input call
		outSql.append(inSql);
		
		// append statement delimitter
		if (!test.endsWith(";"))
			outSql.append(";");
		
		// for now...
		logger.debug("pP:> " + outSql.toString());
		
		// done
		return outSql.toString();
	}
	
	/**
	 * JNDI name attribute
	 * @return
	 */
	public String getJndiName() {
		return jndiName;
	}

	public void setJndiName(String jndiName) {
		this.jndiName = jndiName;
	}
}
